package cn.zxf.note.note.stats;

import cn.zxf.note.note.review.NoteReviewStatusEnum;
import cn.zxf.note.note.stats.dto.KVStatsDto;
import com.github.zengxf.sqlbuilder.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Component;

import java.time.LocalDateTime;
import java.util.List;

/**
 * Created by zengxf on 2019/9/6.
 */
@Component
public class NoteStatsDao {

    @Autowired
    private NamedParameterJdbcTemplate namedJdbc;

    public Integer statsCreateTotal(Integer userId) {
        SqlResult res = SelectBuilder.of()
                .addField(DbField.ofCount("value"))
                .table("note")
                .where(DbCriteria.eq("user_id", userId))
                .build();
        return namedJdbc.queryForObject(res.sql, res.param, Integer.class);
    }

    public List<KVStatsDto> statsReviewTotal(Integer userId) {
        SqlResult res = SelectBuilder.of()
                .addField("status", "name")
                .addField(DbField.ofCount("value"))
                .table("note_review")
                .where(
                        DbCriteria.eq("user_id", userId),
                        DbCriteria.in("status", List.of(NoteReviewStatusEnum.INITIAL.code, NoteReviewStatusEnum.REVIEWED.code))
                )
                .addGroup("status")
                .build();
        RowMapper<KVStatsDto> mapper = BeanPropertyRowMapper.newInstance(KVStatsDto.class);
        return namedJdbc.query(res.sql, res.param, mapper);
    }

    public List<KVStatsDto> statsCreateNote(Integer userId, LocalDateTime[] dates) {
        DbCriteriaGroup where = DbCriteriaGroup.ofAnd(
                DbCriteria.eq("user_id", userId),
                DbCriteria.between("create_date", dates[0], dates[1])
        );
        SqlResult res = SelectBuilder.of()
                .addField("DATE_FORMAT(create_date, '%Y-%m-%d')", "name")
                .addField(DbField.ofCount("value"))
                .table("note")
                .where(where)
                .addGroup("DATE_FORMAT(create_date, '%Y-%m-%d')")
                .setSort(DbSort.of().asc("name"))
                .build();
        RowMapper<KVStatsDto> mapper = BeanPropertyRowMapper.newInstance(KVStatsDto.class);
        return namedJdbc.query(res.sql, res.param, mapper);
    }

    public List<KVStatsDto> statsActualReview(Integer userId, NoteReviewStatusEnum status, LocalDateTime[] arr) {
        String dateField = "reviewed_date";
        if (status == NoteReviewStatusEnum.INITIAL) {
            dateField = "review_date";
        }

        DbCriteriaGroup where = DbCriteriaGroup.ofAnd(
                DbCriteria.eq("user_id", userId),
                DbCriteria.eq("status", status.code),
                DbCriteria.between(dateField, arr[0], arr[1])
        );
        SqlResult res = SelectBuilder.of()
                .addField("DATE_FORMAT(" + dateField + ", '%Y-%m-%d')", "name")
                .addField(DbField.ofCount("value"))
                .table("note_review")
                .where(where)
                .addGroup("DATE_FORMAT(" + dateField + ", '%Y-%m-%d')")
                .setSort(DbSort.of().asc("name"))
                .build();

        RowMapper<KVStatsDto> mapper = BeanPropertyRowMapper.newInstance(KVStatsDto.class);
        return namedJdbc.query(res.sql, res.param, mapper);
    }

    public List<KVStatsDto> statsPlanReview(Integer userId, LocalDateTime[] arr) {
        DbCriteriaGroup where = DbCriteriaGroup.ofAnd(
                DbCriteria.eq("user_id", userId),
                DbCriteria.in("status", List.of(NoteReviewStatusEnum.INITIAL.code, NoteReviewStatusEnum.REVIEWED.code)),
                DbCriteria.between("review_date", arr[0], arr[1])
        );
        SqlResult res = SelectBuilder.of()
                .addField("DATE_FORMAT(review_date, '%Y-%m-%d')", "name")
                .addField(DbField.ofCount("value"))
                .table("note_review")
                .where(where)
                .addGroup("DATE_FORMAT(review_date, '%Y-%m-%d')")
                .setSort(DbSort.of().asc("name"))
                .build();
        RowMapper<KVStatsDto> mapper = BeanPropertyRowMapper.newInstance(KVStatsDto.class);
        return namedJdbc.query(res.sql, res.param, mapper);
    }

}
